﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using KMF.Common;

namespace KMF.DAL
{
    public partial class ProductDb
    {
        public static List<ProductListForPagingDto> GetAllProductPaging(int page, int itemsPerPage, ref int totalPage)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllHasPaging");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter itemTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                itemTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListForPagingDto>(ref command);
                totalPage = int.Parse((itemTotalpage.Value.ToString() == "" ? "0" : itemTotalpage.Value.ToString()));
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductListForPagingDto> GetAllProductHotPaging(int page, int itemsPerPage, ref int totalPage)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_ProductHot_GetAllHasPaging");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter itemTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                itemTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListForPagingDto>(ref command);
                totalPage = int.Parse((itemTotalpage.Value.ToString() == "" ? "0" : itemTotalpage.Value.ToString()));
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static ProductGoldenTimeListDto GetProductGoldenTimeList(string listProduct)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetToGoldenTimeList");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@ListProduct", listProduct));
                var result = dao.GetSingleDTO<ProductGoldenTimeListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductPromotionTopListDto> GetProductPromotionTop5()
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetTop5PromotionProduct");
            try
            {
                var result = dao.GetDTOList<ProductPromotionTopListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        
        /// <summary>
        /// Get top 5 new product list
        /// </summary>
        /// <returns></returns>
        public static List<ProductPromotionTopListDto> GetNewProductTop5()
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetTop5NewProduct");
            try
            {
                var result = dao.GetDTOList<ProductPromotionTopListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListDto> GetAllProductByCateList(string cateList)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCateList");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryList", cateList));
                var result = dao.GetDTOList<ProductByCateTopListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListDto> GetTopProductByCateId(string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByBreadCrumbCategoryId");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                var result = dao.GetDTOList<ProductByCateTopListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductRateByCateTopListPagingDto> GetAllProductByCategoryId(int page, int itemsPerPage, ref int totalPage, string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCategoryId");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static ProductDetailDto GetProductById(string id)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetByProductId");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@ProductId", id));
                var result = dao.GetSingleDTO<ProductDetailDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static ViewProductDetailDto GetProductById_Home(string id)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Home_Product_GetByProductId");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@ProductId", id));
                var result = dao.GetSingleDTO<ViewProductDetailDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static int UpdateTotalView(string productId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_UpdateTotalView");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@ProductId", productId));
                var result = command.ExecuteNonQuery();
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListPagingDto> GetTopProductConcern(string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCateIdTop3");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListDto> GetAllProductByIdList(string productIdList)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByProductIdList");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@ProductIdList", productIdList));
                var result = dao.GetDTOList<ProductByCateTopListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// Select product name by keyword search
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public static List<ProductNameListDto> GetProductNameListByKeyword(string keyword)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllProductNameByKeyword");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Keyword", keyword));
                var result = dao.GetDTOList<ProductNameListDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// Get product list by keyword use fulltext index search
        /// </summary>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="totalPage"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public static List<ProductByCateTopListPagingDto> GetAllProductByKeyword(int page, int itemsPerPage, ref int totalPage, string keyword)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByKeywordFullText");
            try
            {
                var keywords = BuildListKeywordV2(keyword);
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@Condition", keywords));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }


        public static List<ProductByCateTopListPagingDto> GetAllProductByProvider(int page, int itemsPerPage, ref int totalPage, string keyword)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByKeywordFullText");
            try
            {
                var keywords = BuildListKeywordV2(keyword);
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@Condition", keywords));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// Search product in admin 
        /// </summary>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="totalPage"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public static List<ProductListForPagingDto> GetAllProductByKeyWordAdmin(int page, int itemsPerPage, ref int totalPage, string keyword)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByKeywordFullTextAdmin");
            try
            {
                var keywords = BuildListKeywordV2(keyword);
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@Condition", keywords));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListForPagingDto>(ref command);
                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static string BuildListKeywordV2(string keyword)
        {
            var result = string.Empty;
            var words = keyword.Split(new[] { '%', ' ' }, StringSplitOptions.RemoveEmptyEntries).ToList(); //,StringSplitOptions.RemoveEmptyEntries

            var lastKeyword = "";
            lastKeyword += "ISABOUT (\"" + keyword + "\" WEIGHT(0.9)";
            for (int i = 0; i < words.Count; i++)
            {
                lastKeyword += "," + words[i] + " WEIGHT(0.1)";
            }
            lastKeyword += ")";
            result = lastKeyword;
            return result;
        }

        /// <summary>
        /// Get all prodoct promotion
        /// </summary>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="totalPage"></param>
        /// <returns></returns>
        public static List<ProductByCateTopListPagingDto> GetAllProductPromotion(int page, int itemsPerPage, ref int totalPage)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllPromotion");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalPage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalPage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalPage.Value.ToString() == "" ? "0" : outTotalPage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListPagingDto> GetAllProductPromotionByCate(int page, int itemsPerPage, ref int totalPage, string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllPromotionByCate");

            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalPage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalPage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalPage.Value.ToString() == "" ? "0" : outTotalPage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// Get product has discount by categoryid
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static int GetProductDiscountBycate(string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_CountProductDiscount");

            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                var result = dao.GetSingleInt32(command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// Get product new list 
        /// </summary>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="totalPage"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public static List<ProductByCateTopListPagingDto> GetAllProductNew(int page, int itemsPerPage, ref int totalPage)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllNew");
            try
            {
                //var keywords = BuildListKeywordV2(keyword);
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductRateByCateTopListPagingDto> GetAllProducByProvider(int page, int itemsPerPage, ref int totalPage, string ProviderId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetByProvider");
            try
            {
                //var keywords = BuildListKeywordV2(keyword);
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                command.Parameters.Add(dao.CreateParameter("@ProviderId", ProviderId));
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductSynViewDto> GetProductSyn()
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllSyn");
            try
            {
                var result = dao.GetDTOList<ProductSynViewDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static int UpdateProductSyn(string barcode, decimal price, int quantity)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_UpdateProductSyn");

            try
            {
                command.Parameters.Add(dao.CreateParameter("@Barcode", barcode));
                command.Parameters.Add(dao.CreateParameter("@Price", price));
                command.Parameters.Add(dao.CreateParameter("@Quantity", quantity));
                var result = dao.GetSingleInt32(command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }
}
